package com.codeying.dao;

import com.codeying.entity.*;
import com.codeying.utils.*;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;

import java.util.*;
/**
 *  汽车Dao，和数据库互动。
 */
public class CarDao extends AbstractDao<Car>{

    @Override
    protected Class<Car> clazz() {
        return Car.class;
    }

    @Override
    public String tbName(){
        return "tb_car";
    }

    public static CarDao me = new CarDao();

    /**
     * 将map参数转换成sql的where条件语句。
     */
    @Override
    public String toConditionSql(Map<String, String> paramMap){
        StringBuilder sqlBuilder=new StringBuilder("");//接下来拼接查询条件,比如 and xx=xx and XX < xx
        if(paramMap!=null){
                String id = paramMap.get("id");
            if(MyUtils.isNotEmpty(id)){
                        sqlBuilder.append(" and id like '%").append(id).append("%'");// 使用模糊查询
                    }
                    String showpic = paramMap.get("showpic");
            if(MyUtils.isNotEmpty(showpic)){
                        sqlBuilder.append(" and showpic like '%").append(showpic).append("%'");// 使用模糊查询
                    }
                    String showtitle = paramMap.get("showtitle");
            if(MyUtils.isNotEmpty(showtitle)){
                        sqlBuilder.append(" and showtitle like '%").append(showtitle).append("%'");// 使用模糊查询
                    }
                    String showdesc = paramMap.get("showdesc");
            if(MyUtils.isNotEmpty(showdesc)){
                        sqlBuilder.append(" and showdesc like '%").append(showdesc).append("%'");// 使用模糊查询
                    }
                    if(MyUtils.isNotEmpty(paramMap.get("priceL"))){
                sqlBuilder.append(" and price >= ").append(paramMap.get("priceL"));
            }
            if(MyUtils.isNotEmpty(paramMap.get("priceR"))){
                sqlBuilder.append(" and price <= ").append(paramMap.get("priceR"));
            }
                    String showdetail = paramMap.get("showdetail");
            if(MyUtils.isNotEmpty(showdetail)){
                        sqlBuilder.append(" and showdetail like '%").append(showdetail).append("%'");// 使用模糊查询
                    }
                    if(MyUtils.isNotEmpty(paramMap.get("publishtimeL"))){
                sqlBuilder.append(" and publishtime >= '").append(paramMap.get("publishtimeL").replace("T"," ")).append("'");
            }
            if(MyUtils.isNotEmpty(paramMap.get("publishtimeR"))){
                sqlBuilder.append(" and publishtime <= '").append(paramMap.get("publishtimeR").replace("T"," ")).append("'");
            }
                    String carpic = paramMap.get("carpic");
            if(MyUtils.isNotEmpty(carpic)){
                        sqlBuilder.append(" and carpic like '%").append(carpic).append("%'");// 使用模糊查询
                    }
                //排序
            String orderby = paramMap.get("orderby");
            if(orderby!=null){
                sqlBuilder.append(" order by ");
                String[] orderbys = orderby.split(",");
                for (int i = 0 ; i < orderbys.length ; i++) {
                    String s = orderbys[i];
                    sqlBuilder.append(s);
                    if(i!=orderbys.length-1){
                        sqlBuilder.append(",");
                    }
                }
            }
        }
        return sqlBuilder.toString();
    }

    /**
    * 插入
    */
    @Override
    public int insert(Car entity){
        try{
            //构造sql语句
            List<Object> paramList = new ArrayList<>();
            StringBuilder reParamNames = new StringBuilder();
            StringBuilder reParams = new StringBuilder();
            paramList.add(entity.getId());
                if(entity.getShowpic() != null){
                reParamNames.append(",showpic");
                reParams.append(",?");
                paramList.add(entity.getShowpic());
            }
                if(entity.getShowtitle() != null){
                reParamNames.append(",showtitle");
                reParams.append(",?");
                paramList.add(entity.getShowtitle());
            }
                if(entity.getShowdesc() != null){
                reParamNames.append(",showdesc");
                reParams.append(",?");
                paramList.add(entity.getShowdesc());
            }
                if(entity.getPrice() != null){
                reParamNames.append(",price");
                reParams.append(",?");
                paramList.add(entity.getPrice());
            }
                if(entity.getShowdetail() != null){
                reParamNames.append(",showdetail");
                reParams.append(",?");
                paramList.add(entity.getShowdetail());
            }
                if(entity.getPublishtime() != null){
                reParamNames.append(",publishtime");
                reParams.append(",?");
                paramList.add(entity.getPublishtime());
            }
                if(entity.getCarpic() != null){
                reParamNames.append(",carpic");
                reParams.append(",?");
                paramList.add(entity.getCarpic());
            }
                QueryRunner qr = new QueryRunner();  //创建QueryRunner类对象
            //不为空的插入
            String sqlBuilder = "INSERT INTO tb_car (id"+reParamNames.toString()+")VALUES(?"+reParams.toString()+")";
            log(sqlBuilder.toString());
            return qr.update(JdbcUtils.getConnection(), sqlBuilder, paramList.toArray());  // 执行SQL语句
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("CarDao -> insert(-)报错了");
        }
        return 0;
    }

    /**
    * 根据Id更新
    */
    @Override
    public int updateById(Car entity) {
        try{
            QueryRunner qr = new QueryRunner();  //创建QueryRunner类对象
            StringBuilder sqlBuilder = new StringBuilder("update tb_car set id = ? ");
            List<Object> paramList = new ArrayList<>();
            paramList.add(entity.getId());
                    if(entity.getShowpic() != null){
                sqlBuilder.append(", showpic = ? ");
                paramList.add(entity.getShowpic());
            }
                    if(entity.getShowtitle() != null){
                sqlBuilder.append(", showtitle = ? ");
                paramList.add(entity.getShowtitle());
            }
                    if(entity.getShowdesc() != null){
                sqlBuilder.append(", showdesc = ? ");
                paramList.add(entity.getShowdesc());
            }
                    if(entity.getPrice() != null){
                sqlBuilder.append(", price = ? ");
                paramList.add(entity.getPrice());
            }
                    if(entity.getShowdetail() != null){
                sqlBuilder.append(", showdetail = ? ");
                paramList.add(entity.getShowdetail());
            }
                    if(entity.getPublishtime() != null){
                sqlBuilder.append(", publishtime = ? ");
                paramList.add(entity.getPublishtime());
            }
                    if(entity.getCarpic() != null){
                sqlBuilder.append(", carpic = ? ");
                paramList.add(entity.getCarpic());
            }
                //最后拼接id条件
            sqlBuilder.append("where id = ?");
            paramList.add(entity.getId());
            log(sqlBuilder.toString(),paramList);
            return qr.update(JdbcUtils.getConnection(), sqlBuilder.toString(), paramList.toArray());  // 执行SQL语句
        }catch (Exception e){
            e.printStackTrace();
            System.out.println("CarDao -> updateById(-)报错了");
        }
        return 0;
    }

}

